package cn.chinaunicom.sdsi.frm.workflow.traffic.dao.impl;


import cn.chinaunicom.sdsi.frm.workflow.traffic.dao.GeneralDao;
/*import cn.chinaunicom.sdsi.frm.workflow.users.model.UserMonitor;*/
/*import cn.chinaunicom.sdsi.frm.workflow.users.utils.MapShell;*/
import cn.chinaunicom.sdsi.frm.workflow.traffic.pagger.Pagger;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by adinlead on 17-7-4.
 */
@Repository("generalDao")
public class GeneralDaoImpl implements GeneralDao {

    /**
     * Spring提供的JDBC工具
     */
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Map<String, Object>> test() {


        return null;
    }

    @Override
    public List<Map<String, Object>> getGridData() {
        String sql="SELECT t2.* FROM( SELECT t.*, ( t1.lon + t1.lon1) / 2 AS lon, ( t1.lat + t1.lat1 ) / 2 AS lat FROM `traffic_bar_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id ) t2 ";
   /*  String sql="SELECT t2.* FROM( SELECT t.*, t1.lon1 AS lon, t1.lat AS lat FROM `traffic_bar_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id ) t2 ";*/
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getGridHotData(String type) {

        String sql="SELECT  t2.lon, t2.lat, t2.0 FROM( SELECT t.*, ( t1.lon + t1.lon1) / 2 AS lon, ( t1.lat + t1.lat1 ) / 2 AS lat FROM `traffic_hot_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id ) t2  ";
        String sql2="SELECT concat('[', t2.lon,',', t2.lat,',', t2.0,']') as `0` FROM ( SELECT t.*, (t1.lon + t1.lon1) / 2 AS lon, (t1.lat + t1.lat1) / 2 AS lat FROM `traffic_hot_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id ) t2";
        return jdbcTemplate.queryForList(sql2);
    }

    @Override
    public List<Map<String, Object>> getMinLat() {
        String minLat="SELECT t2.* FROM( SELECT t1.lon AS lon, t1.lat1 AS lat FROM `traffic_bar_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id) t2 ORDER BY t2.lat  LIMIT 1";
        return jdbcTemplate.queryForList(minLat);
    }

    @Override
    public List<Map<String, Object>> getMinLon() {
        String minLon="SELECT t2.* FROM( SELECT t1.lon AS lon, t1.lat1 AS lat FROM `traffic_bar_data` t LEFT JOIN traffic_grid_location t1 ON t1.grid_id = t.grid_id) t2 ORDER BY t2.lon LIMIT 1";
        return jdbcTemplate.queryForList(minLon);
    }








    @Override
    public List<Map<String, Object>> getBaseInfoByBaseId(String lac, String ci) {
        lac = lac.trim();
        ci = ci.trim();
        StringBuilder sql = new StringBuilder("select concat(lac+0,'_',ci+0) cell_id,site_name,longitude lng,latitude lat FROM  cell_info where 1=1  ");
        StringBuilder param = new StringBuilder();
        if (lac != "" || lac.length() > 0) {
            param.append("and lac like " + "'%" + lac + "%'");
        }
        if (ci != "" || ci.length() > 0) {
            param.append("and ci like " + "'%" + ci + "%'");
        }
        if (param != null || param.length() > 0) {
            sql.append(param);
        }

        return jdbcTemplate.queryForList(sql.toString());
    }



    /**
     * @param statime 开始时间
     * @param endtime 结束时间
     * @param userid  用户ID
     * @param type    公函类型
     * @return 公函简讯列表
     * @function 获取公函简讯列表
     */
    @Override
    public List<Map<String, Object>> findLitterList(String statime, String endtime, int userid, int type) {
        /*纠正请求参数错误*/
//        if (type == 1)
//            type = 0;
//        else if (type == 0)
//            type = 1;
        /*
         `STATUS`=1 AND
         */
//        String sql = "SELECT b.ELEC_ID, b.NAME FROM gacx.GA_ELEC_APPLY_DOSTAFF a, (SELECT elec_id, NAME FROM gacx.GA_ELEC_APPLY WHERE FLOW_STATUS >= '3' AND to_days(`VALID_START_DATE`) >= to_days(?) AND to_days(`VALID_START_DATE`) <= to_days(?) AND `ELEC_ID` %relationship% ( SELECT MAX(`ELEC_ID`) AS `ELEC_ID` FROM ga_elec_apply_num WHERE STATUS = 1 AND length(`NUM`) > 11 GROUP BY `ELEC_ID` ) ) b WHERE sel_staff_id = ? AND a.elec_id = b.elec_id;";
//        String sql = "SELECT b.ELEC_ID, b.NAME FROM gacx.GA_ELEC_APPLY_DOSTAFF a, (SELECT ELEC_ID, NAME FROM gacx.GA_ELEC_APPLY WHERE FLOW_STATUS >= '3' " +
//                " AND to_days(`VALID_START_DATE`) <= to_days(curdate()) AND to_days(DATE_ADD(`VALID_START_DATE`,INTERVAL 1 MONTH)) > to_days(curdate())" +
//                " AND `ELEC_ID` %relationship% ( SELECT MAX(`ELEC_ID`) AS `ELEC_ID` FROM ga_elec_apply_num WHERE STATUS = 1 AND length(`NUM`) > 11 GROUP BY `ELEC_ID` ) ) b WHERE sel_staff_id = ? AND a.elec_id = b.elec_id;";
        String sql = "SELECT b.ELEC_ID, b.NAME FROM gacx.GA_ELEC_APPLY_DOSTAFF a, (SELECT `ELEC_ID`, `NAME`, `APPLY_DATE`, `SELECT_TYPE` FROM gacx.GA_ELEC_APPLY WHERE FLOW_STATUS >= '3' AND TO_DAYS(`APPLY_DATE`) <= TO_DAYS(CURDATE()) AND TO_DAYS(DATE_ADD(`APPLY_DATE`, INTERVAL 1 MONTH)) > TO_DAYS(CURDATE()) ) b WHERE a.sel_staff_id = ? %relationship% AND a.elec_id = b.elec_id ORDER BY b.APPLY_DATE DESC;";
        if (type == 0) {
            sql = sql.replace("%relationship%", " AND (b.SELECT_TYPE = 0 OR b.SELECT_TYPE IS NULL)");
        } else if (type == 1) {
            sql = sql.replace("%relationship%", " AND b.SELECT_TYPE = 1");
        } else {
            sql = sql.replace("%relationship%", "");
        }
//        return jdbcTemplate.queryForList(sql, statime, endtime, userid);
        return jdbcTemplate.queryForList(sql, userid);
    }

    //    @Override
//    public List<Map<String, Object>> findLitterList(String statime, String endtime, int userid, int type) {
//        /*
//         `STATUS`=1 AND
//         */
//        String sql = "SELECT ELEC_ID, NAME FROM ga_elec_apply WHERE `APPLY_STAFF_ID`=? AND to_days(`APPLY_DATE`) >= to_days(?) AND to_days(`APPLY_DATE`) <=to_days(?) AND `ELEC_ID` %relationship% (SELECT max(`ELEC_ID`) AS `ELEC_ID` FROM ga_elec_apply_num WHERE status=1 AND length(`NUM`) > 11 GROUP BY `ELEC_ID`);";
//        if (type == 1) {
//            sql = sql.replace("%relationship%", "IN");
//        } else {
//            sql = sql.replace("%relationship%", "NOT IN");
//        }
//        return jdbcTemplate.queryForList(sql, new Object[]{userid, statime, endtime});
//    }

    /**
     * @return 省份简讯列表
     * @function 获取省份简讯列表
     */
    @Override
    public List<Map<String, Object>> getProvList() {
        String sql = "SELECT prov_id,prov_show_name FROM ga_dim_prov;";
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * @param prov 省份代码
     * @return 城市简讯列表
     * @funcion 根据省份获取城市简讯列表
     */
    @Override
    public List<Map<String, Object>> getCityList(String prov) {
      /*  String sql = "SELECT dis_id,area_code,city_show_name FROM ga_dim_city WHERE ord_id IS NOT NULL AND prov_id LIKE ?";*/
        String sql = "SELECT dis_id,area_code,city_show_name FROM ga_dim_city WHERE  prov_id LIKE ?";
        return jdbcTemplate.queryForList(sql, prov);
    }

    /**
     * @param prov 省份代码
     * @return 城市简讯列表
     * @funcion 根据省份获取城市简讯列表
     */
    @Override
    public List<Map<String, Object>> getCityList2(String prov) {
        String sql = "SELECT city_id,area_code,city_show_name FROM ga_dim_city WHERE prov_id LIKE ?";
        return jdbcTemplate.queryForList(sql, prov);
    }

    /**
     * @param userid 用户ID
     * @return 基站列表
     * @function 根据用户查询基站列表
     */
    @Override
    public List<Map<String, Object>> getAreaListByUserGroup(Integer userid) {
        String sql = "SELECT c.* FROM gacx.sys_staff a, gacx.sys_staff b, gacx.ga_area_baseinfo c WHERE a.staff_id = ? AND a.org_id = b.org_id AND b.staff_id = c.staff_id AND c.is_valid = '1' AND  c.showType='0';";
        return jdbcTemplate.queryForList(sql, userid);
    }

    /**
     * @param city 城市代码
     * @return 基站列表
     * @function 根据城市获取基站列表
     */
    @Override
    public List<Map<String, Object>> getAreaListByCity(String city) {
        String sql = "SELECT area_id,area_name FROM ga_area_baseinfo WHERE city_id=?";
        return jdbcTemplate.queryForList(sql, city);
    }

    /**
     * @param city 城市代码
     * @return 地区简讯列表
     * @function 根据城市获取地区简讯列表
     */
    @Override
    public List<Map<String, Object>> getDistrictByCity(String city) {
        String sql = "SELECT `district_code` AS `id`,`district_show_name` AS `name` FROM `ga_dim_district` WHERE `city_id`=?";
        return jdbcTemplate.queryForList(sql, city);
    }

    /**
     * @param ids 根据地区代码列表
     * @return 地区简讯列表
     * @function 根据地区代码列表获得地区简讯列表
     */
    @Override
    public List<Map<String, Object>> findAddressName(Object[] ids) {
        List<Map<String, Object>> result = new ArrayList<>();
//        在地区表中查找
        StringBuilder sql = new StringBuilder("SELECT district_code AS k,district_show_name AS v FROM ga_dim_district where district_code in (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(");");
        result.addAll(jdbcTemplate.queryForList(sql.toString(), ids));
//        在城市表中查询
        sql = new StringBuilder("SELECT area_code AS k,city_show_name AS v FROM ga_dim_city where city_id in (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(");");
        result.addAll(jdbcTemplate.queryForList(sql.toString(), ids));
//        在省份表中查询
        sql = new StringBuilder("SELECT prov_id AS k,prov_show_name AS v FROM ga_dim_prov WHERE prov_id in (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(");");
        result.addAll(jdbcTemplate.queryForList(sql.toString(), ids));
        return result;
    }

    /**
     * @param userid 用户ID
     * @return 用户信息
     * @function 根据ID获得用户的信息
     */
    @Override
    public List<Map<String, Object>> getLoginUserInfo(Integer userid) {
        String sql = "SELECT a.staff_id, a.staff_name, a.org_id, b.dis_id, c.dis_name , c.dis_level, d.area_code, CASE WHEN d.area_code IN ('9999', '9001') THEN '济南' ELSE c.dis_name END AS dis_name2, CASE WHEN d.area_code IN ('9999', '9001') THEN '0531' ELSE d.area_code END AS area_code2 FROM sys_staff a, sys_org_dis b, sys_dis c, ga_dim_city d WHERE a.staff_id = ? AND a.org_id = b.org_id AND b.dis_id = c.dis_id AND b.dis_id = d.dis_id";
        return jdbcTemplate.queryForList(sql, userid);
    }

    /**
     * @param citycode 城市代码
     * @return 关联地区细信息
     * @function 根据城市代码获取关联地区细信息
     */
    @Override
    public Map<String, Object> getLoginUserAddressInfoByCity(String citycode) {
        String sql = "SELECT ga_dim_prov.prov_id AS provid, ga_dim_city.area_code AS cityid, ga_dim_prov.prov_show_name AS provname, ga_dim_city.city_show_name AS cityname FROM ga_dim_city LEFT JOIN ga_dim_prov ON ga_dim_city.prov_id = ga_dim_prov.prov_id WHERE ga_dim_city.area_code = ?;";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, citycode);
        if (result != null && result.size() > 0)
            return result.get(0);
        return null;
    }

    /**
     * @param page         页码
     * @param rows         每页行数
     * @param phone        手机号码
     * @param remark       备注
     * @param org
     * @param state        状态
     * @param admin        查询者是否为管理员
     * @param loginStaffId 查询者ID    @return 红名单用户列表
     * @function 获得红名单用户列表
     */
    @Override
    public Pagger getUserReadList(int page, int rows, String phone, String remark, String org, String state,String create_person,String sel_dis, boolean admin, Integer loginStaffId) {
        int star = (page - 1) * rows;
        StringBuilder paramStr = new StringBuilder();
        List<String> paramArr = new ArrayList<>();
        if ((phone != null && phone.length() > 0) || (remark != null && remark.length() > 0) || (state != null && state.length() > 0)|| (org != null && org.length() > 0)|| (create_person != null && create_person.length() > 0)|| (sel_dis != null && sel_dis.length() > 0)) {
            paramStr.append(" WHERE 1=1 ");
            if (phone != null && phone.length() > 0) {
                phone = "%" + phone + "%";
                paramStr.append(" AND user LIKE ? ");
                paramArr.add(phone);
            }
            if (remark != null && remark.length() > 0) {
                remark = "%" + remark + "%";
                paramStr.append(" AND remark LIKE ? ");
                paramArr.add(remark);
            }
            if (state != null && state.length() > 0 && !"1,0".equalsIgnoreCase(state) && !"0,1".equalsIgnoreCase(state)) {
                paramStr.append(" AND state in (#STATE#) ".replace("#STATE#", state));
            }

            if (create_person != null && create_person.length() > 0) {
                paramStr.append(" AND sys_staff.STAFF_ID = ? ");
                paramArr.add(create_person);
            }
            if (org != null && org.length() > 0) {
                paramStr.append(" AND sys_org.PARENT_ID = ? ");
                paramArr.add(org);
            }
            if (sel_dis != null && sel_dis.length() > 0) {
                paramStr.append(" AND ga_dim_city.DIS_ID = ? ");
                paramArr.add(sel_dis);
            }
        }
        if (!admin) {
            if (paramStr.indexOf("WHERE") < 0) {
                paramStr.append(" WHERE operator = ?");
            } else {
                paramStr.append(" AND operator = ?");
            }
            paramArr.add(String.valueOf(loginStaffId));
        } else {
            if (TextUtils.notEmpty(org)) {
                paramStr.append(" AND sys_org.PARENT_ID =  ?");
                paramArr.add(org);
            }
        }
        Pagger pagger;
        if (paramStr.length() > 0) {
            StringBuilder count;
            StringBuilder find;
            count = new StringBuilder("SELECT COUNT(*) FROM ga_user_read LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_read.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ");
            count.append(paramStr);
            find = new StringBuilder("SELECT _id, USER, state, remark, date_format(ceratetime, '%Y%m%d%H%i%S') AS ceratetime, date_format(updatetime, '%Y%m%d%H%i%S') AS updatetime, operator, sys_staff.STAFF_NAME, sys_staff.STAFF_ID, sys_staff.ORG_ID, sys_org.PARENT_ID, ga_dim_city.DIS_ID, ga_dim_city.city_show_name as DIS_NAME, toporg.org_id AS org_code, toporg.org_name AS org_name, ifnull( ( SELECT ORG_NAME FROM sys_org WHERE ORG_ID = ga_user_read.operator_org LIMIT 1 ), '' ) AS org, operator_org FROM ga_user_read LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_read.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ");
            find.append(paramStr);
            find.append(" LIMIT ?,?");
            List<java.io.Serializable> paramlist = new ArrayList<>();
            paramlist.addAll(paramArr);
            paramlist.add(star);
            paramlist.add(rows);
            pagger = new Pagger(
                    page,
                    rows,
                    jdbcTemplate.queryForObject(count.toString(), Long.class, paramArr.toArray()),
                    jdbcTemplate.queryForList(find.toString(), paramlist.toArray()));
        } else {
            pagger = new Pagger(
                    page,
                    rows,
                    jdbcTemplate.queryForObject("SELECT COUNT(*) ga_user_read LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_read.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ", Long.class),
                    jdbcTemplate.queryForList("SELECT _id, USER, state, remark, date_format(ceratetime, '%Y%m%d%H%i%S') AS ceratetime, date_format(updatetime, '%Y%m%d%H%i%S') AS updatetime, operator, sys_staff.STAFF_NAME, sys_staff.STAFF_ID, sys_staff.ORG_ID, sys_org.PARENT_ID, ga_dim_city.DIS_ID, ga_dim_city.city_show_name as DIS_NAME, toporg.org_id AS org_code, toporg.org_name AS org_name, ifnull( ( SELECT ORG_NAME FROM sys_org WHERE ORG_ID = ga_user_read.operator_org LIMIT 1 ), '' ) AS org, operator_org FROM ga_user_read LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_read.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID   LIMIT ?,?", star, rows));
        }
        return pagger;
    }

    /**
     * @param page    页码
     * @param rows    每页行数
     * @param phone   手机号码
     * @param remark  备注
     * @param org     单位
     * @param state   状态
     * @param admin   查询者是否为管理员
     * @param staffId 查询者ID    @return 红名单用户列表
     * @function 获得黑名单用户列表
     */
    @Override
    public Pagger getUserBlackList(int page, int rows, String phone, String remark, String org, String state, String create_person,String sel_dis,boolean admin, Integer staffId) {
        int star = (page - 1) * rows;
        StringBuilder paramStr = new StringBuilder();
        List<String> paramArr = new ArrayList<>();
        if ((phone != null && phone.length() > 0) || (remark != null && remark.length() > 0) || (state != null && state.length() > 0)|| (org != null && org.length() > 0)|| (create_person != null && create_person.length() > 0)|| (sel_dis != null && sel_dis.length() > 0)) {
            paramStr.append(" WHERE 1=1 ");
            if (phone != null && phone.length() > 0) {
                phone = "%" + phone + "%";
                paramStr.append(" AND user LIKE ? ");
                paramArr.add(phone);
            }
            if (remark != null && remark.length() > 0) {
                remark = "%" + remark + "%";
                paramStr.append(" AND remark LIKE ? ");
                paramArr.add(remark);
            }
            if (state != null && state.length() > 0 && !"1,0".equalsIgnoreCase(state) && !"0,1".equalsIgnoreCase(state)) {
                paramStr.append(" AND state in (#STATK#) ".replace("#STATK#", state));
            }
            if (create_person != null && create_person.length() > 0) {
                paramStr.append(" AND sys_staff.STAFF_ID = ? ");
                paramArr.add(create_person);
            }
            if (org != null && org.length() > 0) {
                paramStr.append(" AND sys_org.PARENT_ID = ? ");
                paramArr.add(org);
            }
            if (sel_dis != null && sel_dis.length() > 0) {
                paramStr.append(" AND ga_dim_city.DIS_ID = ? ");
                paramArr.add(sel_dis);
            }
        }
        if (!admin) {
            if (paramStr.indexOf("WHERE") < 0) {
                paramStr.append(" WHERE operator = ?");
            } else {
                paramStr.append(" AND operator = ?");
            }
            paramArr.add(String.valueOf(staffId));
        } else {
            if (TextUtils.notEmpty(org)) {
                paramStr.append(" AND sys_org.PARENT_ID =  ?");
                paramArr.add(org);
            }
        }
        Pagger pagger;
        if (paramStr.length() > 0) {
            StringBuilder count;
            StringBuilder find;
            count = new StringBuilder("SELECT COUNT(*) FROM ga_user_black LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_black.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ");
            count.append(paramStr);
            find = new StringBuilder("SELECT _id, USER, state, remark, date_format(ceratetime, '%Y%m%d%H%i%S') AS ceratetime, date_format(updatetime, '%Y%m%d%H%i%S') AS updatetime, operator, sys_staff.STAFF_NAME, sys_staff.STAFF_ID, sys_staff.ORG_ID, sys_org.PARENT_ID, ga_dim_city.DIS_ID, ga_dim_city.city_show_name as DIS_NAME, toporg.org_id AS org_code, toporg.org_name AS org_name, ifnull( ( SELECT ORG_NAME FROM sys_org WHERE ORG_ID = ga_user_black.operator_org LIMIT 1 ), '' ) AS org FROM ga_user_black LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_black.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ");
            find.append(paramStr);
            find.append(" LIMIT ?,?");
            List<java.io.Serializable> paramlist = new ArrayList<>();
            paramlist.addAll(paramArr);
            paramlist.add(star);
            paramlist.add(rows);
            System.out.println("+++++++++++++++++++++++"+paramlist.toArray());
            System.out.println("99999999999999999999999999999"+paramArr);
            pagger = new Pagger(
                    page,
                    rows,
                    jdbcTemplate.queryForObject(count.toString(), Long.class, paramArr.toArray()),
                    jdbcTemplate.queryForList(find.toString(), paramlist.toArray()));
        } else {
            pagger = new Pagger(
                    page,
                    rows,
                    jdbcTemplate.queryForObject("SELECT COUNT(*) FROM ga_user_black LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_black.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  ", Long.class),
                    jdbcTemplate.queryForList("SELECT _id, USER, state, remark, date_format(ceratetime, '%Y%m%d%H%i%S') AS ceratetime, date_format(updatetime, '%Y%m%d%H%i%S') AS updatetime, operator, sys_staff.STAFF_NAME, sys_staff.STAFF_ID, sys_staff.ORG_ID, sys_org.PARENT_ID, ga_dim_city.DIS_ID, ga_dim_city.city_show_name as DIS_NAME, toporg.org_id AS org_code, toporg.org_name AS org_name, ifnull( ( SELECT ORG_NAME FROM sys_org WHERE ORG_ID = ga_user_black.operator_org LIMIT 1 ), '' ) AS org FROM ga_user_black LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = ga_user_black.operator LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = sys_org.ORG_ID LEFT JOIN `sys_org_dis` ON `sys_org`.`ORG_ID` = `sys_org_dis`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `ga_dim_city`.`DIS_ID` = `sys_org_dis`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID  LIMIT ?,?", star, rows));
        }
        return pagger;
    }

    /**
     * @param page    页码
     * @param rows    每页行数
     * @param state   状态
     * @param dis
     * @param org
     * @param admin   查询者是否为管理员
     * @param staffId 查询者ID
     * @param name
     * @param remark  @return 特殊人员分类列表     @function 获得特殊人员分类列表
     * @param staff
     */
    @Override
    public Pagger getUserMonitorTypeList(int page, int rows, String state, String dis, String org, boolean admin, Integer staffId, String name, String remark, String staff) {
        int star = (page - 1) * rows;
        StringBuilder sql = new StringBuilder("SELECT ga_user_monitor_type.id, ga_user_monitor_type. NAME as name, IFNULL( ga_user_monitor_type.remark, '' ) AS remark, ga_user_monitor_type.state, DATE_FORMAT( ga_user_monitor_type.createtime, '%Y%m%d%H%i%S' ) AS createtime, DATE_FORMAT( ga_user_monitor_type.updatetime, '%Y%m%d%H%i%S' ) AS updatetime, ga_user_monitor_type.staffid, sys_staff.STAFF_NAME AS staff_name, sys_org.ORG_NAME AS temp_name, ga_dim_city.city_show_name AS dis_name, sys_org.PARENT_ID AS PARENT_ID, toporg.org_id AS org_id, toporg.org_name AS org_name FROM `ga_user_monitor_type` LEFT JOIN sys_staff ON ga_user_monitor_type.staffid = sys_staff.STAFF_ID LEFT JOIN sys_org ON sys_staff.ORG_ID = sys_org.ORG_ID LEFT JOIN sys_org_dis ON sys_org.ORG_ID = sys_org_dis.ORG_ID LEFT JOIN ga_dim_city ON ga_dim_city.DIS_ID = sys_org_dis.DIS_ID LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID WHERE 1 = 1  ");
        StringBuilder cun = new StringBuilder("SELECT COUNT(id) FROM  `ga_user_monitor_type` LEFT JOIN sys_staff ON ga_user_monitor_type.staffid = sys_staff.STAFF_ID LEFT JOIN sys_org ON sys_staff.ORG_ID = sys_org.ORG_ID LEFT JOIN sys_org_dis ON sys_org.ORG_ID = sys_org_dis.ORG_ID LEFT JOIN ga_dim_city ON ga_dim_city.DIS_ID = sys_org_dis.DIS_ID LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID WHERE 1 = 1  ");
        if (state != null && state.length() > 0) {
            sql.append(" AND `ga_user_monitor_type`.`state`=").append(state);
            cun.append(" AND `ga_user_monitor_type`.`state`=").append(state);
        }
        if (TextUtils.notEmpty(name)) {
            name = "'%" + name + "%'";
            sql.append(" AND `ga_user_monitor_type`.name LIKE ").append(name);
            cun.append(" AND `ga_user_monitor_type`.name LIKE ").append(name);
        }
        if (TextUtils.notEmpty(remark)) {
            remark = "'%" + remark + "%'";
            sql.append(" AND `ga_user_monitor_type`.remark LIKE ").append(remark);
            cun.append(" AND `ga_user_monitor_type`.remark LIKE ").append(remark);

        }
        if (!admin) {
            sql.append(" AND `ga_user_monitor_type`.staffid = ").append(TextUtils.toSQLString(staffId));
            cun.append(" AND `ga_user_monitor_type`.staffid = ").append(TextUtils.toSQLString(staffId));
        }
        if (TextUtils.notEmpty(dis)) {
            sql.append(" AND `ga_dim_city`.DIS_ID = ").append(dis);
            cun.append(" AND `ga_dim_city`.DIS_ID = ").append(dis);
        }
        if (TextUtils.notEmpty(org)) {
            sql.append(" AND sys_org.PARENT_ID = ").append(org);
            cun.append(" AND sys_org.PARENT_ID= ").append(org);
        }
        if (TextUtils.notEmpty(staff)) {
            sql.append(" AND sys_staff.STAFF_NAME LIKE '%").append(staff).append("%'");
            cun.append(" AND sys_staff.STAFF_NAME LIKE '%").append(staff).append("%'");
        }
        sql.append(" LIMIT ?,?");

        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(cun.toString(), Long.class),
                jdbcTemplate.queryForList(sql.toString(), star, rows)
        );
    }

    /**
     * @param state 状态
     * @param phone
     * @return 执行状态
     * @function 增加特殊人员分类
     */
    @Override
    public Pagger getUserMonitorListByType(int page, int rows, int type, String state, String phone) {
        int star = (page - 1) * rows;
        StringBuilder sql = new StringBuilder("SELECT id, type, phone, remark, state, date_format(createtime,'%Y%m%d%H%i%S') AS createtime, date_format(updatetime,'%Y%m%d%H%i%S') AS updatetime, staffid FROM `ga_user_monitor_list` WHERE `type`=");
        StringBuilder cun = new StringBuilder("SELECT count(*) FROM `ga_user_monitor_list` WHERE `type`=");
        sql.append(type);
        cun.append(type);
        if (state != null && state.length() > 0) {
            sql.append(" AND `state`=").append(state);
            cun.append(" AND `state`=").append(state);
        }
        if (TextUtils.notEmpty(phone)) {
            phone = "'%" + phone + "%'";
            sql.append(" AND phone LIKE " + phone);
            cun.append(" AND phone LIKE " + phone);
        }
        sql.append(" LIMIT ?,?");
        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(cun.toString(), Long.class),
                jdbcTemplate.queryForList(sql.toString(), star, rows)
        );
    }

    @Override
    public int addUserMonitorType(int state, String name, String remark, int staffid) {
        String sql = "INSERT INTO `ga_user_monitor_type` (`state`,`name`, `remark`, `updatetime`, `staffid`) VALUES (? ,?, ?, ?, ?);";
        return jdbcTemplate.update(sql, state, name, remark, new Date(), staffid);
    }

    @Override
    public int editUserMonitorType(int id, String name, String remark, int state, int staffId) {
        String sql = "UPDATE `ga_user_monitor_type` SET `name`=?, `remark`=?, `state`=?, `updatetime`=? WHERE `id`=? AND `staffid`=?;";
        return jdbcTemplate.update(sql, name, remark, state, new Date(), id, staffId);
    }

    @Override
    public int updateUserMonitorTypeState(int id, int state, int staffId) {
        String sql = "UPDATE `ga_user_monitor_type` SET `state`=?, `updatetime`=? WHERE `id`=? AND `staffid`=?;";
        return jdbcTemplate.update(sql, state, new Date(), id, staffId);
    }

    @Override
    public boolean isMyMonitorType(int id, int staffId) {
        String sql = "SELECT COUNT(id) FROM `ga_user_monitor_type` WHERE `id`=? AND `staffid`=?;";
        return jdbcTemplate.queryForObject(sql, new Object[]{id, staffId}, Long.class) > 0;
    }



    @Override
    public int removeUserMonitorType(int id) {
        String removeSub = "DELETE FROM `ga_user_monitor_list` WHERE `type`=?";
        jdbcTemplate.update(removeSub, id);
        String sql = "DELETE FROM `ga_user_monitor_type` WHERE `id`=?;";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int removeUserMonitorList(Object[] ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM `ga_user_monitor_list` WHERE `id` in ( ?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(", ?");
        }
        sql.append(");");
        return jdbcTemplate.update(sql.toString(), ids);
    }

    @Override
    public List<Map<String, Object>> getMonitorTypeUsers(int tyid) {
        String sql = "SELECT `phone` FROM `ga_user_monitor_list` WHERE `type`=?;";
        return jdbcTemplate.queryForList(sql, tyid);
    }

    //    向红名单中添加用户
    @Override
    public int addUserReadList(Map<String, String> users, Long operator, Long operator_org) {
        Set<String> keySet = users.keySet();
        List<Map<String, Object>> result = findUsers(keySet);
        List<String> saveList = new ArrayList<>();
        for (Map<String, Object> m : result) {
            saveList.add(String.valueOf(m.get("user")));
        }
        for (String k : saveList) {
            keySet.remove(k);
        }
        saveList.clear();

        if (keySet.size() > 0) {
            Date currDate = new Date();
            saveList.addAll(keySet);
            StringBuilder sqlTemplate = new StringBuilder("INSERT INTO `ga_user_read` (`user`, `remark`, `updatetime`, `operator`, `operator_org`) VALUES (?, ?, ?, ?, ?)");
            List<Object> paramArr = new ArrayList<>();
            String k = saveList.get(0);
            String v = users.get(k);
            paramArr.add(k);
            paramArr.add(v);
            paramArr.add(currDate);
            paramArr.add(operator);
            paramArr.add(operator_org);
            for (int i = 1; i < saveList.size(); i++) {
                sqlTemplate.append(",(?, ?, ?, ?)");
                k = saveList.get(i);
                v = users.get(k);
                paramArr.add(k);
                paramArr.add(v);
                paramArr.add(currDate);
                paramArr.add(operator);
            }
            return jdbcTemplate.update(sqlTemplate.toString(), paramArr.toArray());
        }
        return 0;
    }

    //    向黑名单中添加用户
    @Override
    public int addUserBlackList(Map<String, String> users, Long operator, Long operator_org) {
        Set<String> keySet = users.keySet();
        List<Map<String, Object>> result = findUsers(keySet);
        List<String> saveList = new ArrayList<>();
        for (Map<String, Object> m : result) {
            saveList.add(String.valueOf(m.get("user")));
        }
        for (String k : saveList) {
            keySet.remove(k);
        }
        saveList.clear();

        if (keySet.size() > 0) {
            Date currDate = new Date();
            saveList.addAll(keySet);
            StringBuilder sqlTemplate = new StringBuilder("INSERT INTO `ga_user_black` (`user`, `remark`, `updatetime`, `operator`, `operator_org`) VALUES (?, ?, ?, ?, ?)");
            List<Object> paramArr = new ArrayList<>();
            String k = saveList.get(0);
            String v = users.get(k);
            paramArr.add(k);
            paramArr.add(v);
            paramArr.add(currDate);
            paramArr.add(operator);
            paramArr.add(operator_org);
            for (int i = 1; i < saveList.size(); i++) {
                k = saveList.get(i);
                v = users.get(k);
                paramArr.add(k);
                paramArr.add(v);
                paramArr.add(currDate);
                paramArr.add(operator);
                sqlTemplate.append(",(?, ?, ?, ?)");
            }
            return jdbcTemplate.update(sqlTemplate.toString(), paramArr.toArray());
        }
        return 0;
    }

    @Override
    public int deleteUserReadList(String[] ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM `ga_user_read` WHERE `_id` in (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(")");

        return jdbcTemplate.update(sql.toString(), (Object[]) ids);
    }

    @Override
    public int deleteUserBlackList(String[] ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM `ga_user_black` WHERE `_id` in (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(")");
        return jdbcTemplate.update(sql.toString(), (Object[]) ids);
    }

    //    获取处于红黑名单中的用户，确保气不会重复
    private List<Map<String, Object>> findUsers(Set<String> keySet) {
        List<Map<String, Object>> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT user FROM #TABLENAME# WHERE user in (?");
        for (int i = 1; i < keySet.size(); i++) {
            sql.append(",?");
        }
        sql.append(");");
        result.addAll(jdbcTemplate.queryForList(sql.toString().replace("#TABLENAME#", "ga_user_read"), keySet.toArray()));
        result.addAll(jdbcTemplate.queryForList(sql.toString().replace("#TABLENAME#", "ga_user_black"), keySet.toArray()));
        return result;
    }

    public Long countCity(String staffId) {
        String sql = "select count(1) from  gacx.ga_area_baseinfo  where staffId=" + staffId;
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public int insertBaseInfo(String staff_id, String city_id, String area_id, String area_name, Date create_date, int is_valid, int area_type, String showType, int baseType) {
        String sql = "INSERT INTO ga_area_baseinfo (staff_id,city_id,area_id,area_name,create_date,is_valid,area_type,showType,baseType) VALUES (?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql, staff_id, city_id, area_id, area_name, create_date, is_valid, area_type, showType, baseType);
    }

    @Override
    public int insertPoint(String areaId, Double lng1, Double lat1, Double lng2, Double lat2, Double lng3, Double lat3, Double lng4, Double lat4) {
        String sql = "INSERT INTO gacx.ga_area_pointinfo(area_id,lng1,lat1,lng2,lat2,lng3,lat3,lng4,lat4) VALUES (?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql, areaId, lng1, lat1, lng2, lat2, lng3, lat3, lng4, lat4);
    }

    @Override
    public int insertCircle(String areaId, Double lng1, Double lat1, Double radius) {
        String sql = "INSERT INTO gacx.ga_area_pointinfo(area_id,lng1,lat1,radius) VALUES (?,?,?,?)";
        return jdbcTemplate.update(sql, areaId, lng1, lat1, radius);
    }

    @Override
    public int insertLacinfo(String baseid, List<String[]> lacarr) {
        StringBuilder sql = new StringBuilder("INSERT INTO `ga_area_lacinfo` (`area_id`, `lac`, `ci`) VALUES ");
        for (String[] lac : lacarr) {
            sql.append("(");
            sql.append(baseid);
            sql.append(",");
            sql.append(lac[0]);
            sql.append(",");
            sql.append(lac[1]);
            sql.append("),");
        }
        sql.deleteCharAt(sql.lastIndexOf(","));
        System.out.println("============ SQL ===============");
        System.out.println(sql);
//        return 0;
        return jdbcTemplate.update(sql.toString());
    }

    @Override
    public Pagger findFence(int page, int rows, boolean isadmin, Integer orgid) {
        String find = "SELECT ga_area_baseinfo.*,sys_staff_org.ORG_ID AS org_id FROM gacx.ga_area_baseinfo left join sys_staff_org on ga_area_baseinfo.staff_id = sys_staff_org.STAFF_ID  where gacx.ga_area_baseinfo.showType='0'";
        String cunt = "SELECT COUNT(0) FROM gacx.ga_area_baseinfo left join sys_staff_org on ga_area_baseinfo.staff_id = sys_staff_org.STAFF_ID where gacx.ga_area_baseinfo.showType='0'";
        if (!isadmin) {
            find += "  and sys_staff_org.ORG_ID = ";
            find += TextUtils.toSQLString(String.valueOf(orgid));
            cunt += "  and sys_staff_org.ORG_ID = ";
            cunt += orgid;
        }
        find += "  LIMIT ?,?";

        int start = (page - 1) * rows;

        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(cunt, Long.class),
                jdbcTemplate.queryForList(find, start, rows));


    }

    @Override
    public Pagger findPoint(int page, int rows, String area_id) {
        String sql = "SELECT * FROM gacx.ga_area_pointinfo where  area_id=" + area_id;
        int start = (page - 1) * rows;
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        Map<String, Object> map1 = new HashMap<String, Object>();
        Map<String, Object> map2 = new HashMap<String, Object>();
        Map<String, Object> map3 = new HashMap<String, Object>();
        Map<String, Object> map4 = new HashMap<String, Object>();

        Map<String, Object> temp = new HashMap<String, Object>();

        if (list.isEmpty() || list == null) {
            sql = "SELECT * FROM ga_area_lacinfo WHERE area_id=? LIMIT ?,?";
            return new Pagger(
                    page,
                    rows,
                    jdbcTemplate.queryForObject("SELECT COUNT(1) FROM ga_area_lacinfo where  area_id=" + area_id, Long.class),
                    jdbcTemplate.queryForList(sql, area_id, start, rows));
        }
        temp = list.get(0);
        if (temp.get("radius") == null) {
            map1.put("_id", temp.get("_id"));
            map1.put("lng", temp.get("lng1").toString());
            map1.put("lat", temp.get("lat1").toString());
            map1.put("radius", temp.get("radius"));

            map2.put("_id", temp.get("_id"));
            map2.put("lng", temp.get("lng2").toString());
            map2.put("lat", temp.get("lat2").toString());

            map3.put("_id", temp.get("_id"));
            map3.put("lng", temp.get("lng3").toString());
            map3.put("lat", temp.get("lat3").toString());

            map4.put("_id", temp.get("_id"));
            map4.put("lng", temp.get("lng4").toString());
            map4.put("lat", temp.get("lat4").toString());

            list.clear();
            list.add(map1);
            list.add(map2);
            list.add(map3);
            list.add(map4);
        } else {
            map1.put("_id", temp.get("_id"));
            map1.put("lng", temp.get("lng1").toString());
            map1.put("lat", temp.get("lat1").toString());
            map1.put("radius", temp.get("radius").toString());
            list.clear();
            list.add(map1);
        }


        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject("SELECT COUNT(1) FROM ga_area_pointinfo where  area_id=" + area_id, Long.class),
                list);
    }

    @Override
    public int deleteFence(String area_id) {
        String sql = "update ga_area_baseinfo set showType='1' where area_id=" + area_id;
        return jdbcTemplate.update(sql);
    }

    @Override
    public int deletePoint(String area_id) {
        String sql = "delete from ga_area_pointinfo where area_id=" + area_id;
        return jdbcTemplate.update(sql);
    }

    @Override
    public String findCity(String staff_id) {
        String sql = "select distinct city_id from ga_area_baseinfo where  staff_id=" + staff_id;

        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public Integer findMaxAreaId(String city_id) {
        String sql = "select Max(area_id+0) from ga_area_baseinfo where  city_id=" + city_id;

        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<Map<String, Object>> exeSQL(String sql, Object... param) {
        if (param != null && param.length > 0)
            return jdbcTemplate.queryForList(sql, param);
        else
            return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int enables(String area_id) {
        String sql = "update ga_area_baseinfo set is_valid=1 where area_id=" + area_id;
        return jdbcTemplate.update(sql);
    }

    @Override
    public int disables(String area_id) {
        String sql = "update ga_area_baseinfo set is_valid=0 where area_id=" + area_id;
        return jdbcTemplate.update(sql);
    }

    @Override
    public Pagger log_retention(int page, int rows, String staff_Name, String elec_id, Date operDate1, Date operDate2, String actionName, Integer staffId) {

        return null;
    }

    @Override
    public Pagger checkNumber(int page, int rows, String idx) {
        String sql = "select a.num from gacx.ga_sys_select_detail_log a  where  a.id=" + idx;
        String count = "SELECT count(1) FROM gacx.ga_sys_select_detail_log a  where  a.id=" + idx;
        int start = (page - 1) * rows;

        Pagger pagger = new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(count, Long.class),
                jdbcTemplate.queryForList(sql));
        return pagger;

    }

    @Override
    public Pagger checkNumberByElecId(int page, int rows, String elecid) {
        String sql = "SELECT num FROM ga_elec_apply_num WHERE elec_id=?";
        String count = "SELECT count(1) FROM ga_elec_apply_num WHERE elec_id=?";
        int start = (page - 1) * rows;

        Pagger pagger = new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(count, new Object[]{elecid}, Long.class),
                jdbcTemplate.queryForList(sql, new Object[]{elecid}));
        return pagger;
    }

    @Override
    public Map<String, Object> selectConfigValue(String code) {
        String sql = "SELECT `val` FROM `sys_config` WHERE `code`=?";
        return jdbcTemplate.queryForMap(sql, code);
    }

    @Override
    public List<Map<String, Object>> getCompany() {
        String sql = "SELECT org_id,org_name FROM sys_org WHERE org_type='1'  AND org_kind='2'";
        return jdbcTemplate.queryForList(sql);

    }

    @Override
    public Pagger policeInfo(int page, int rows, String org_name, Date check_time1, Date check_time2, String is_valid) {
        StringBuilder sql = new StringBuilder("select b.org_name,a.check_time,a.is_valid from (select t1.org_id,t1.is_valid,t1.check_time from ga_sys_org_check t1 union all select t2.org_id,t2.is_valid,t2.check_time from ga_sys_org_check_his t2 )a,(select org_id,org_name from sys_org where org_type='1'  and org_kind='2')b where a.org_id=b.org_id   ");
        StringBuilder count = new StringBuilder("select count(1) from (select t1.org_id,t1.is_valid,t1.check_time from ga_sys_org_check t1 union all select t2.org_id,t2.is_valid,t2.check_time from ga_sys_org_check_his t2 )a,(select org_id,org_name from sys_org where org_type='1'  and org_kind='2')b where a.org_id=b.org_id ");

       /* and  org_name="济南市公安局" and check_time between '2017-07-26' and  '2017-07-28'  and is_valid=0;*/
        int start = (page - 1) * rows;

        StringBuilder buf = new StringBuilder();
        if (org_name != null && org_name.length() > 0) {
            buf.append("and  org_name='").append(org_name).append("'");
        }
        SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd");

        if ((check_time1 != null)) {
            buf.append("and to_days(check_time) between  to_days('").append(dateFormater.format(check_time1)).append("')");
        } else {
            Calendar c = Calendar.getInstance();
            c.add(Calendar.MONTH, 0);
            c.set(Calendar.DAY_OF_MONTH, 1);//设置为1号,当前日期既为本月第一天
            check_time1 = c.getTime();
            buf.append("and to_days(check_time) between  to_days('").append(dateFormater.format(check_time1)).append("')");
        }

        if ((check_time2 != null)) {
            buf.append("and to_days('").append(dateFormater.format(check_time2)).append("')");
        } else {
            check_time2 = new Date();
            buf.append("and to_days('").append(dateFormater.format(check_time2)).append("')");
        }

        if (is_valid != null && is_valid.length() > 0) {
            buf.append("and is_valid= ").append(is_valid);
        }
        sql.append(buf);
        count.append(buf);
        sql.append("  limit ?,?");

        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(count.toString(), Long.class),
                jdbcTemplate.queryForList(sql.toString(), start, rows)
        );
    }

    @Override
    public Map<String, Object> getOrgMessage(String org_id) {
        String sql = "SELECT a.org_id, a.org_name, d.area_code, d.city_name FROM sys_org a, sys_org_dis b, sys_dis c, ga_dim_city d WHERE a.org_id = b.org_id AND b.dis_id = c.dis_id AND c.dis_id = d.dis_id AND a.org_type = '1' AND a.org_kind = '2' AND a.org_id = ?;";
        return jdbcTemplate.queryForMap(sql, org_id);
    }

    @Override
    public List<Map<String, Object>> getOrgList() {
        String sql = "SELECT org_id,org_name FROM sys_org WHERE org_type='1' AND org_kind='2'";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public Pagger getFTPConfigList(int page, int rows, String org, String area, String dir, String state) {
        StringBuilder param = new StringBuilder();
        if (TextUtils.notEmpty(org)) {
            param.append(" AND org_id = ");
            param.append(TextUtils.toSQLString(org));
        }
        if (TextUtils.notEmpty(area)) {
            param.append(" AND city_id = ");
            param.append(TextUtils.toSQLString(area));
        }
        if (TextUtils.notEmpty(dir)) {
            param.append(" AND dir LIKE ");
            param.append(TextUtils.toSQLString(dir));
        }
        if (TextUtils.notEmpty(state) && !state.contains(",")) {
            param.append(" AND state =");
            param.append(state);
        }
        int start = (page - 1) * rows;
        StringBuilder query = new StringBuilder("SELECT `id`, `org_id`, `org_name`, `city_id`, `city_name`, `ip`, `username`, `password`, `local`, `dir`, `state`, DATE_FORMAT(`createtime`, '%Y%m%d%H%i%S') AS `createtime`, DATE_FORMAT(`updatetime`, '%Y%m%d%H%i%S') AS `updatetime` FROM ga_sys_ftp_info WHERE del = 0");
        StringBuilder count = new StringBuilder("SELECT COUNT(`id`) FROM ga_sys_ftp_info WHERE del = 0");
        query.append(param);
        count.append(param);
        query.append(" LIMIT ?,?");
        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(count.toString(), Long.class),
                jdbcTemplate.queryForList(query.toString(), start, rows)
        );
    }

    @Override
    public int addFTPConfig(String org_id, String org_name, String city_id, String city_name, String ip, String username, String password, String local, String dir, int state, int staffId) {
        String sql = "INSERT INTO `ga_sys_ftp_info` (`org_id`, `org_name`, `city_id`, `city_name`, `ip`, `username`, `password`,`local`, `dir`, `state`, `createtime`, `staffid`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        return jdbcTemplate.update(sql, org_id, org_name, city_id, city_name, ip, username, password, local, dir, state, new Date(), staffId);
    }

    /*@Override
    public int updateFTPConfig(int id, String org_id, String org_name, String city_id, String city_name, String ip, String username, String password, String dir, int state, int staffId) {
        if (TextUtils.notEmpty(password)) {
            String sql = "UPDATE `ga_sys_ftp_info` SET `org_id`=?, `org_name`=?, `city_id`=?, `city_name`=?, `ip`=?, `username`=?, `password`=?, `dir`=?, `state`=?, `updatetime`=?, `staffid`=? WHERE `id`=? AND del=0;";
            return jdbcTemplate.update(sql, org_id, org_name, city_id, city_name, ip, username, password, dir, state, new Date(), staffId, id);
        } else {
            String sql = "UPDATE `ga_sys_ftp_info` SET `org_id`=?, `org_name`=?, `city_id`=?, `city_name`=?, `ip`=?, `username`=?, `dir`=?, `state`=?, `updatetime`=?, `staffid`=? WHERE `id`=? AND del=0;";
            return jdbcTemplate.update(sql, org_id, org_name, city_id, city_name, ip, username, dir, state, new Date(), staffId, id);
        }
    }*/

    @Override
    public int updateFTPConfig(int id, String ip, String username, String password, String local,String dir, int state, int staffId) {
       /* if (TextUtils.notEmpty(password)) {*/
            String sql = "UPDATE `ga_sys_ftp_info` SET `ip`=?, `username`=?, `password`=?, `local`=?,`dir`=?, `state`=?, `updatetime`=?, `staffid`=? WHERE `id`=? AND del=0;";
            return jdbcTemplate.update(sql, ip, username, password,local, dir, state, new Date(), staffId, id);
       /* } else {
            String sql = "UPDATE `ga_sys_ftp_info` SET `org_id`=?, `org_name`=?, `city_id`=?, `city_name`=?, `ip`=?, `username`=?, `dir`=?, `state`=?, `updatetime`=?, `staffid`=? WHERE `id`=? AND del=0;";
            return jdbcTemplate.update(sql, ip, username, dir, state, new Date(), staffId, id);
        }*/
    }

    @Override
    public int updateFTPConfigState(int[] ids, int state) {
        StringBuilder sql = new StringBuilder("UPDATE `ga_sys_ftp_info` SET `state`=? WHERE `id` IN (?");
        List<Integer> params = new ArrayList<Integer>();
        params.add(state);
        params.add(ids[0]);
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
            params.add(ids[i]);
        }
        sql.append(") AND del=0;");
        return jdbcTemplate.update(sql.toString(), params.toArray());
    }

    @Override
    public int removeFTPConfig(int id) {
        String sql = "UPDATE `ga_sys_ftp_info` SET `del`='1' WHERE `id`= ? AND del=0;";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public List<Map<String, Object>> getPhoneByElecId(String elec_id) {
        String sql = "SELECT a.elec_id,a.num,b.name,ifnull(b.APPLY_DESC,'') AS apply_desc FROM gacx.ga_elec_apply_num a,ga_elec_apply b WHERE a.ELEC_ID=b.ELEC_ID  AND a.ELEC_ID= ?";
        return jdbcTemplate.queryForList(sql, elec_id);

    }

    @Override
    public List<Map<String, Object>> showTablesName() {
        return jdbcTemplate.queryForList("SHOW TABLES;");
    }

    @Override
    public Pagger getUrlList(int page, int rows, boolean admin, Integer staffId) {
        int star = (page - 1) * rows;
        String find = "SELECT id, url, remark, date_format(intime,'%Y%m%d%H%i%S') AS intime, (select sys_staff.STAFF_NAME from sys_staff where sys_staff.STAFF_ID = ga_url_rule.instaff limit 1) AS instaff, inorg FROM ga_url_rule";
        String cunt = "SELECT COUNT(id) FROM ga_url_rule";
        if (!admin) {
            find += " WHERE instaff=" + TextUtils.toSQLString(staffId);
            cunt += " WHERE instaff=" + TextUtils.toSQLString(staffId);
        }
        find += " LIMIT ?,?;";
        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(cunt, Long.class),
                jdbcTemplate.queryForList(find, star, rows)
        );
    }

    @Override
    public JSONObject addUrlRule(String[] url, String[] remarks, Object instaff, Object inorg) {
        JSONObject result = new JSONObject();
        if (url.length > 0) {
            StringBuilder sql = new StringBuilder("INSERT INTO `ga_url_rule` (`url`, `remark`, `instaff`, `inorg`) VALUES (?, ?, ?, ?)");
            List<Object> param = new ArrayList<>();
            param.add(url[0]);
            param.add(remarks[0]);
            param.add(instaff);
            param.add(inorg);
            for (int i = 1; i < url.length; i++) {
                sql.append(",(?, ?, ?, ?)");
                param.add(url[i]);
                param.add(remarks[i]);
                param.add(instaff);
                param.add(inorg);
            }
            result.put("success", jdbcTemplate.update(sql.toString(), param.toArray()));
        } else {
            result.put("success", 0);
        }
        return result;
    }

    @Override
    public int delUrlRule(Object[] ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM `ga_url_rule` WHERE `id` IN (?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(",?");
        }
        sql.append(");");
        return jdbcTemplate.update(sql.toString(), ids);
    }

    @Override
    public Pagger getSMSLog(int page, int rows, String orgmessage, String staffname, String startime, String endtime, String task, String dis, boolean isAdmin, Integer staffId) {
        StringBuilder param = new StringBuilder();
        if (TextUtils.notEmpty(orgmessage)) {
            param.append(" AND  d.ORG_ID = ");
            param.append(TextUtils.toSQLString(orgmessage));
        }
        if (TextUtils.notEmpty(staffname)) {
            staffname = "%" + staffname + "%";
            param.append(" AND b.staff_name like  ");
            param.append(TextUtils.toSQLString(staffname));
        }
        if (TextUtils.notEmpty(startime)) {
            if (TextUtils.notEmpty(endtime)) {
                param.append(" AND unix_timestamp(a.to_date) >= unix_timestamp('" + startime + "') AND unix_timestamp(a.to_date) <= unix_timestamp('" + endtime + "')");
            } else {
                param.append(" AND unix_timestamp(a.to_date) >= unix_timestamp('" + startime + "')");
            }
        }

        if (TextUtils.notEmpty(task)) {
            param.append(" AND a.task_id = ").append(TextUtils.toSQLString(task));
        }

        if (!isAdmin) {
            param.append(" AND a.staff_id = ");
            param.append(TextUtils.toSQLString(staffId));
        }

        if (TextUtils.notEmpty(dis)) {
            param.append(" AND f.dis_id = ").append(TextUtils.toSQLString(dis));
        }

        int start = (page - 1) * rows;
        StringBuilder query = new StringBuilder("SELECT d.org_name AS org_name, a.staff_id AS staff_id, b.staff_name AS staff_name, a.send_num AS send_num, DATE_FORMAT(a.to_date, '%Y%m%d%H%i%S') AS createtime, a.mess AS mess, e.remark, g.city_show_name as dis_name FROM GA_SEND_MESS a, sys_staff b, sys_org c, sys_org d, ga_task e, sys_org_dis f, ga_dim_city g WHERE a.staff_id = b.staff_id AND b.org_id = c.org_id AND c.parent_id = d.org_id AND e.task_id = a.task_id AND c.org_id = f.org_id AND f.dis_id = g.dis_id");


        StringBuilder count = new StringBuilder("select count(1) FROM GA_SEND_MESS a, sys_staff b, sys_org c, sys_org d, ga_task e, sys_org_dis f, ga_dim_city g WHERE a.staff_id = b.staff_id AND b.org_id = c.org_id AND c.parent_id = d.org_id AND e.task_id = a.task_id AND c.org_id = f.org_id AND f.dis_id = g.dis_id ");

        query.append(param);
        count.append(param);
        query.append(" LIMIT ?,?");
        System.out.println("=================================");
        System.out.println(query.toString());
        return new Pagger(
                page,
                rows,
                jdbcTemplate.queryForObject(count.toString(), Long.class),
                jdbcTemplate.queryForList(query.toString(), start, rows)
        );

    }

    @Override
    public JSONArray getEuosarList(String org, String status) {
        JSONArray result = new JSONArray();
        String sql = "SELECT ga_log_euosar.id, ga_log_euosar.org, ga_log_euosar.AOP, ga_log_euosar.file, DATE_FORMAT( ga_log_euosar.createtime, '%Y%m%d%H%i%S' ) AS createtime, ( SELECT sys_staff.STAFF_NAME FROM sys_staff WHERE sys_staff.STAFF_ID = ga_log_euosar.instaff LIMIT 1 ) AS instaff, ga_log_euosar. STATUS, ga_dim_city.city_show_name AS dis, ga_dim_city.DIS_ID AS disId FROM ga_log_euosar, sys_org, sys_org_dis, ga_dim_city WHERE ga_log_euosar.org = sys_org.ORG_NAME AND sys_org.ORG_ID = sys_org_dis.ORG_ID AND sys_org_dis.DIS_ID = ga_dim_city.DIS_ID";
        if (TextUtils.notEmpty(org)) {
            sql += " AND ga_dim_city.DIS_ID=" + TextUtils.toSQLString(org);
        }
        if (TextUtils.notEmpty(status)) {
            sql += " AND status=" + status;
        }
        result.addAll(jdbcTemplate.queryForList(sql));
        return result;
    }

    @Override
    public int addEuosar(String org, String aop, String file, Integer staffId, Integer orgId) {
        return jdbcTemplate.update("INSERT INTO `ga_log_euosar` (`org`, `AOP`, `file`, `instaff`, `inorg`) VALUES ( ?, ?, ?, ?, ?);", org, aop, file, staffId, orgId);
    }

    @Override
    public int delEuosar(String[] ids) {
        return 0;
    }

    @Override
    public int banEuosar(String[] ids, Boolean ban) {
        int isban = ban ? 0 : 1;
        StringBuilder sql = new StringBuilder("UPDATE `ga_log_euosar` SET `status`='" + isban + "' WHERE `id` IN ( ?");
        for (int i = 1; i < ids.length; i++) {
            sql.append(" ,?");
        }
        sql.append(")");
        return jdbcTemplate.update(sql.toString(), (Object) ids);
    }

    @Override
    public List<Map<String, Object>> getAlarmList(String taskid, String findate, String findateend, String read, String org, String dis, String staff, boolean isAdmin, Integer staffId) {
        String sql = "SELECT `ga_send_mess`.`id`, `ga_send_mess`.`staff_id`, `sys_staff`.`STAFF_NAME` AS `staff`, sys_org.PARENT_ID, toporg.org_id AS org_code, toporg.org_name AS org, `sys_org`.`ORG_ID` AS `org_id`, `sys_org`.`ORG_CODE` AS `org_code`, `ga_send_mess`.`to_num`, `ga_send_mess`.`mess`, DATE_FORMAT( `ga_send_mess`.`to_date`, '%Y%m%d%H%i%S' ) AS `to_date`, `ga_send_mess`.`send_num`, `ga_send_mess`.`is_send`, `ga_send_mess`.`is_read`, `ga_send_mess`.`job_id`, `ga_send_mess`.`task_id`, `ga_send_mess`.`type`, `ga_send_mess`.`status`, `ga_send_mess`.`remark`, `ga_task_config`.`job_name`, `ga_dim_city`.`city_show_name` as dis_name FROM `ga_send_mess` LEFT JOIN `ga_task_config` ON `ga_send_mess`.`job_id` = `ga_task_config`.`job_id` LEFT JOIN `sys_staff` ON `sys_staff`.`STAFF_ID` = `ga_send_mess`.`staff_id` LEFT JOIN `sys_org` ON `sys_staff`.`ORG_ID` = `sys_org`.`ORG_ID` LEFT JOIN `sys_org_dis` ON `sys_org_dis`.`ORG_ID` = `sys_org`.`ORG_ID` LEFT JOIN `ga_dim_city` ON `sys_org_dis`.`DIS_ID` = `ga_dim_city`.`DIS_ID` LEFT JOIN ( SELECT org_id, org_name FROM sys_org WHERE org_type = '1' AND org_kind = '2' ) toporg ON toporg.org_id = sys_org.PARENT_ID WHERE `ga_send_mess`.`type` = 1  ";

        if (!isAdmin) {
            sql += " AND `ga_send_mess`.`staff_id` = ";
            sql += TextUtils.toSQLString(staffId);
        }
        if (TextUtils.notEmpty(read)) {
            sql += "  AND `ga_send_mess`.`is_read` = ";
            sql += read;
        } else if (TextUtils.isEmpty(taskid) && TextUtils.isEmpty(findate) && TextUtils.isEmpty(findateend)) {
            sql += "  AND `ga_send_mess`.`is_read` = 0";
        }
        if (TextUtils.notEmpty(findate)) {
            sql += " AND UNIX_TIMESTAMP(`ga_send_mess`.`to_date`) >= UNIX_TIMESTAMP(" + TextUtils.toSQLString(findate) + ")";
        }
        if (TextUtils.notEmpty(findateend)) {
            sql += " AND UNIX_TIMESTAMP(`ga_send_mess`.`to_date`) <= UNIX_TIMESTAMP(" + TextUtils.toSQLString(findateend) + ")";
        }
        if (TextUtils.notEmpty(taskid)) {
            sql += " AND `ga_task_config`.`job_id` = " + taskid;
        }
        if (TextUtils.notEmpty(org)) {
            sql += " AND sys_org.PARENT_ID =" + org;
        }
        if (TextUtils.notEmpty(dis)) {
            sql += " AND `ga_dim_city`.`DIS_ID`=" + dis;
        }
        if (TextUtils.notEmpty(staff)) {
            staff = '%' + staff + '%';
            sql += " AND `sys_staff`.`STAFF_NAME` LIKE ";
            sql += TextUtils.toSQLString(staff);
        }
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getRoleList(Integer staffid) {
        String sql = "SELECT sys_staff_role.* FROM sys_staff_org RIGHT JOIN sys_staff_role ON sys_staff_org.STAFF_ORG_ID = sys_staff_role.STAFF_ORG_ID WHERE sys_staff_org.STAFF_ID = ? ORDER BY sys_staff_role.ROLE_ID;";
        return jdbcTemplate.queryForList(sql, staffid);
    }

    @Override
    public int updateReadOrBlackUsersState(Object[] idArr, String state, String table) {
        String sql = "UPDATE `" + table + "` SET `state`=" + TextUtils.toSQLString(state) + " WHERE `_id` IN ( ?";
        for (int i = 1; i < idArr.length; i++) {
            sql += ",? ";
        }
        sql += ");";
        return jdbcTemplate.update(sql, idArr);
    }

    @Override
    public List<Map<String, Object>> getAPIInfoList(String name, String remark, String elec, String type) {
        String sql = "SELECT api_id, api_code, api_name, api_method, api_description, DATE_FORMAT(create_time,'%Y%m%d%H%i%S') AS create_time, iselec, elec_check_type, isdisplay FROM ga_sys_api_info WHERE 1=1";
        if (TextUtils.notEmpty(name)) {
            name = '%' + name + '%';
            sql += " AND api_name LIKE " + TextUtils.toSQLString(name);
        }
        if (TextUtils.notEmpty(remark)) {
            remark = '%' + remark + '%';
            sql += " AND api_description LIKE " + TextUtils.toSQLString(remark);
        }
        if (TextUtils.notEmpty(elec)) {
            sql += " AND iselec = " + elec;
        }
        if (TextUtils.notEmpty(type)) {
            sql += " AND elec_check_type = " + type;
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int addApiInfo(String api_code, String api_method, String api_name, String api_description, String isdisplay, String iselec, String chtype) {
        String sql = "INSERT INTO `gacx`.`ga_sys_api_info` (`api_code`, `api_name`, `api_method`, `api_description`, `create_time`, `iselec`, `isdisplay`, `elec_check_type`) VALUES (?, ?, ?, ?, now(), ?, ?, ?);";
        return jdbcTemplate.update(sql, api_code, api_method, api_name, api_description, isdisplay, iselec, chtype);
    }

    @Override
    public int editApiInfo(String api_id, String api_code, String api_method, String api_name, String api_description, String isdisplay, String iselec) {
        String sql = "UPDATE `ga_sys_api_info` SET `api_code`=?, `api_name`=?, `api_method`=?, `api_description`=?, `iselec`=?, `isdisplay`=? WHERE `api_id`=?;";
        return jdbcTemplate.update(sql, api_code, api_name, api_method, api_description, iselec, isdisplay, api_id);
    }

    @Override
    public int deleteAPIInfo(String api_id) {
        return jdbcTemplate.update("DELETE FROM `gacx`.`ga_sys_api_info` WHERE `api_id`=?;", api_id);
    }

    @Override
    public List<Map<String, Object>> checkAPIInfoList(String apiname, String apiInfo, String letterNeed, String jurisdictionNeed, String buff) {
        StringBuilder sql = new StringBuilder("SELECT api_id, api_code, api_name, api_method, api_description, DATE_FORMAT(create_time,'%Y%m%d%H%i%S') AS create_time, iselec, isdisplay FROM ga_sys_api_info  where 1=1");
        StringBuilder param = new StringBuilder();
        if (!apiname.isEmpty()) {
            param.append("  and api_method like  " + "'%" + apiname + "%'");
        }
        if (!apiInfo.isEmpty()) {
            param.append("  and api_name like " + "'%" + apiInfo + "%'");
        }
        if (!letterNeed.isEmpty()) {
            param.append("  and iselec=" + letterNeed);
        }
        sql.append(param);

        return jdbcTemplate.queryForList(sql.toString());

    }

    @Override
    public List<Map<String, Object>> getSmsUsers(boolean isadmin, Integer orgid) {


        String sql = "SELECT sys_staff.staff_id,staff_name FROM sys_staff left join sys_staff_org on sys_staff.staff_id = sys_staff_org.STAFF_ID where MOBIL_PHONE IS NOT NULL and 1=1  ";
        if (!isadmin) {
            sql += " and sys_staff_org.ORG_ID =  ";
            sql += TextUtils.toSQLString(String.valueOf(orgid));

        }

        return jdbcTemplate.queryForList(sql);


    }

    @Override
    public Integer saveLac(String sendData) {
        JSONArray lacData = JSON.parseArray(sendData);
        int num = 0;
        int temp = 0;
        String sql = "insert into gacx.ga_area_lacinfo (area_id,lac,ci) values(?,?,?)";
        for (int i = 0; i < lacData.size(); i++) {
            String areaId = lacData.getJSONObject(i).getString("area_id");
            String lac = lacData.getJSONObject(i).getString("lac");
            String ci = lacData.getJSONObject(i).getString("ci");
            temp = jdbcTemplate.update(sql, areaId, lac, ci);
            num += temp;
        }
        return num;
    }

    @Override
    public Pagger getPushAPILog(int servic_type, int page, int rows) {
        int star = (page - 1) * rows;
        String find = "SELECT ID, STAFF_ID, FUNC_CODE, FUNC_NAME, DATE_FORMAT(OPER_DATE, '%Y-%c-%d %h:%i:%s' ) AS OPER_DATE, ELEC_ID, SELECT_TYPE, IP_ADDR, ATTR_ONE, ATTR_TWO, ATTR_THREE, REMARK, METHOD, STATUS, INVOKETYPE, SERVICE_TYPE, CHECK_TYPE, ORG_ID FROM ga_sys_select_log_check_num WHERE SERVICE_TYPE = ? LIMIT ?,?;";
        String cunt = "SELECT COUNT(*) FROM ga_sys_select_log_check_num WHERE SERVICE_TYPE = ?;";
        return new Pagger(page, rows,
                jdbcTemplate.queryForObject(cunt, Long.class, servic_type),
                jdbcTemplate.queryForList(find, servic_type, star, rows));
    }

    @Override
    public JSONArray getTaskList() {
        JSONArray array = new JSONArray();
        array.addAll(jdbcTemplate.queryForList("SELECT task_Id,remark FROM ga_task"));
        return array;
    }

    @Override
    public List getSysDisList() {
        return jdbcTemplate.queryForList("SELECT dis_id,dis_name FROM sys_dis  where PARENT_ID like '37%';");
    }
}

class TextUtils {
    public static boolean notEmpty(String s) {
        return s != null && s.length() > 0;
    }

    public static boolean isEmpty(String s) {
        return s == null || s.length() == 0;
    }

    static String toSQLString(String s) {
        return "'" + s + "'";
    }

    static String toSQLString(Integer staffId) {
        return toSQLString(String.valueOf(staffId));
    }
}


